package yu.zhou.njupt.pdsmigration;

import java.io.DataOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;

import oracle.sql.BLOB;

import org.apache.commons.dbutils.DbUtils;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import yu.zhou.njupt.pdsmigration.model.GenerateImageTypeEnum;
import yu.zhou.njupt.pdsmigration.utils.StringUtil;

/**
 * @author yu.zhou
 * 
 */
@SuppressWarnings("unused")
public class DataTransfer {
    
    private static final Logger logger = LoggerFactory.getLogger(DataTransfer.class);
    private static final Properties CONFIGURE = new Properties();
    private static String IMAGE_EXPORT_PATH = null; // 文件生成路径
    private static final SAXReader XML_READER = new SAXReader();
    private static Document PROCEDURE_DOC = null;
    private static Map<String, String> GENERATE_IMAGE_SQL_MAP = new HashMap<String, String>(); 
    private static final Map<String, String> TABLE_MAP = new HashMap<String, String>();
    private static final String QUERY_STR = "SELECT * FROM {0}";   
    private static final String INSERT_STR = "INSERT INTO {0} VALUES ({1})";
    private static final String CALL_DEAL_DATA_STR = "CALL DEAL_DATA()";
    private static Connection SOURCE_CONNECTION = null;
    private static Connection TARGET_CONNECTION = null;

    public DataTransfer() {
        try {
            logger.info("数据迁移开始。。。");
            
            Date startTime = new Date();
            init();
//            createDataObject();
            importData2();
//            dealData();
//            doExtra();
//            generateImages(GenerateImageTypeEnum.GENERATE_FULL_NAME_USE_ROWID);
            Date endTime = new Date();
            
            logger.info("数据迁移成功！");
            logger.info("总耗时：" + (endTime.getTime() - startTime.getTime()) / 1000 + "秒");
        } catch (Exception e) {
            logger.error("数据迁移失败!!!");
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(SOURCE_CONNECTION);
            DbUtils.closeQuietly(TARGET_CONNECTION);
        }
    }
    
    protected void init() throws Exception {
    	try {
    		InputStream configFileInStream = getClass().getResourceAsStream("/config.properties");
    		CONFIGURE.load(configFileInStream);

    		/* 建立于源数据库、目标数据库的连接 */
    		logger.info("开始连接源数据库与目标数据库...");
    		Class.forName(CONFIGURE.getProperty("source.driverClass"));
    		Class.forName(CONFIGURE.getProperty("target.driverClass"));
    		SOURCE_CONNECTION = DriverManager.getConnection(CONFIGURE.getProperty("source.url"), CONFIGURE.getProperty("source.username"), CONFIGURE.getProperty("source.password"));
    		TARGET_CONNECTION = DriverManager.getConnection(CONFIGURE.getProperty("target.url"), CONFIGURE.getProperty("target.username"), CONFIGURE.getProperty("target.password"));
    		logger.info("连接源数据库与目标数据库成功!");

    		/* 建立原表与目标表的映射 */
    		for (String str : CONFIGURE.getProperty("tableMap").split(",")) {
    			int index = str.lastIndexOf("-");
    			TABLE_MAP.put(str.substring(0, index), str.substring(index + 1));
    		}

    		IMAGE_EXPORT_PATH = CONFIGURE.getProperty("imageExportPath");
    		
    		/* 读取生成图片的SQL语句的文件 */
    		Document generateImageDoc = XML_READER.read(DataTransfer.class.getClassLoader().getResourceAsStream("生成图片.xml"));
    		Element rootElement = generateImageDoc.getRootElement();
    		for (Object obj : rootElement.elements()) {
                Element element = (Element) obj;
                GENERATE_IMAGE_SQL_MAP.put(element.attributeValue("id"), element.getText());
            }
    	} catch (Exception e) {
            logger.error("连接源数据库与目标数据库失败!!!");
            throw e;
        }
    }

    /** 导入数据前创建表以及存储过程 */
	private void createDataObject() throws Exception {
		logger.info("开始执行procedure.xml中的PL/SQL程序段：");
		PROCEDURE_DOC = XML_READER.read(getClass().getResourceAsStream("/procedure.xml"));
		for (Object object : PROCEDURE_DOC.getRootElement().elements()) {
			Element element = (Element) object;
			CallableStatement cs = TARGET_CONNECTION.prepareCall(element.getText());
			cs.execute();
			logger.info(element.attributeValue("id") + " SUCCESS!");
		}
		logger.info("procedure.xml中的PL/SQL程序段执行结束");
    }
    
    /** 将数据从源数据库导入到目标数据库 */
    protected void importData() throws Exception {
        String sourceTableName = null;
        String targetTableName = null;
        
        try {
            logger.info("开始导入数据...");
            logger.info("原表：" + sourceTableName + " --> 目标表：" + targetTableName + "开始");

            for (Entry<String, String> entry : TABLE_MAP.entrySet()) {
                int count = 0;
                sourceTableName = entry.getKey();
                targetTableName = entry.getValue();
                logger.info("原表：" + sourceTableName + " --> 目标表：" + targetTableName + "开始");
                Statement queryStatement = SOURCE_CONNECTION.createStatement();
                ResultSet queryResult = queryStatement.executeQuery(MessageFormat.format(QUERY_STR, sourceTableName));
                int columnCount = queryResult.getMetaData().getColumnCount();
                PreparedStatement insertStatement = TARGET_CONNECTION
                        .prepareStatement(MessageFormat.format(INSERT_STR,
                                targetTableName,
                                StringUtil.getPlaceHolderStr(columnCount)));
                while (queryResult.next()) {
                    for(int i = 1; i <= columnCount; i++){
                        insertStatement.setObject(i, queryResult.getObject(i));
                    }
                    insertStatement.addBatch();
                    count ++;
                    while (count == 1000) {
                        logger.info("插入1000条数据至" + targetTableName);
                        insertStatement.executeBatch();
                        insertStatement.clearBatch();
                        count = 0;
                    }
                }
                insertStatement.executeBatch();

                DbUtils.closeQuietly(queryResult);
                DbUtils.closeQuietly(queryStatement);
                DbUtils.closeQuietly(insertStatement);
                logger.info("原表：" + sourceTableName + " --> 目标表：" + targetTableName + "成功！");
            }

            logger.info("导入数据成功！");
        } catch (Exception e) {
            logger.error("原表：" + sourceTableName + " --> 目标表：" + targetTableName + "失败!!!");
            throw e;
        }
    }
    
    /** 将数据从源数据库导入到目标数据库 */
    protected void importData2() throws Exception {
        String sourceTable = CONFIGURE.getProperty("sourceTable");
        String sourceColumns = CONFIGURE.getProperty("sourceColumns");
        String targetTable = CONFIGURE.getProperty("targetTable");
        int recordCount = 0;

        try {
            logger.info("开始导入数据...");
            logger.info("  原表：" + sourceTable + " --> 目标表：" + targetTable);

            int count = 0;
            Statement queryStatement = SOURCE_CONNECTION.createStatement();
            String querySqlSql = "select " + sourceColumns + " from " + sourceTable;
            ResultSet queryResult = queryStatement.executeQuery(querySqlSql);
            int columnCount = queryResult.getMetaData().getColumnCount();
            PreparedStatement insertStatement = TARGET_CONNECTION
                    .prepareStatement(MessageFormat.format(INSERT_STR,
                            targetTable,
                            StringUtil.getPlaceHolderStr(columnCount)));
            while (queryResult.next()) {
                for(int i = 1; i <= columnCount; i++){
                    insertStatement.setObject(i, queryResult.getObject(i));
                }
                insertStatement.addBatch();
                count ++;
                if (count == 100000) {
                    insertStatement.executeBatch();
                    insertStatement.clearBatch();
                    recordCount += count;
                    count = 0;
                    logger.info("    已插入" + recordCount + "条数据");
                }
            }
            insertStatement.executeBatch();

            DbUtils.closeQuietly(queryResult);
            DbUtils.closeQuietly(queryStatement);
            DbUtils.closeQuietly(insertStatement);
            logger.info("  原表：" + sourceTable + " --> 目标表：" + targetTable + "成功！");

            logger.info("导入数据成功！");
        } catch (Exception e) {
            logger.error("  原表：" + sourceTable + " --> 目标表：" + targetTable + "失败!!!");
            throw e;
        }
    }
    
    /** 对导入到目标数据库的数据进行处理,这里主要就是调用存储过程 */
    protected void dealData() throws Exception {
    	CallableStatement cs = null; 
        try {
            logger.info("开始处理导入后数据。。。");
 
            cs = TARGET_CONNECTION.prepareCall(CALL_DEAL_DATA_STR);
            cs.execute();
            DbUtils.closeQuietly(cs);
            
            logger.info("处理数据成功！");
        } catch (Exception e) {
        	DbUtils.closeQuietly(cs);
            throw e;
        }
    }
    
	private void executeProcedure(Element procedure) throws Exception {
        CallableStatement cs = null;
        String procedureName = procedure.attributeValue("id");
        
        try {
            logger.info("存储过程[" + procedureName + "]开始执行" + "。。。");
            cs = TARGET_CONNECTION.prepareCall(procedure.getText());
            cs.execute();
            logger.info("存储过程[" + procedureName + "]执行成功！");
        } catch (Exception e) {
            logger.error("存储过程[" + procedureName + "]执行失败!!!");
            throw e;
        } finally {
            DbUtils.closeQuietly(cs);
        }
    }

    protected void doExtra() throws Exception{}
    
    /** 执行传入的SQ语句,根据结果集在指定目录生成图片 */
    private void generateImages(String imageExportPathStr, GenerateImageTypeEnum generateImageType) throws Exception{
        String filePathStr = imageExportPathStr;
        String queryStr = GENERATE_IMAGE_SQL_MAP.get(generateImageType.getName());
        System.out.println(queryStr);
        PreparedStatement ps = null;
        ResultSet rs = null;
        String fileName = null;
        DataOutputStream dos = null;
        int count = 0; // 图文件为空的记录数

        try {
            logger.info("开始生成全部配件图片，生成图片目录为" + filePathStr + "，请稍候。。。");
            
            File filePath = new File(filePathStr);
            if (!filePath.exists()) {
                filePath.mkdirs();
            }else {
                for (String child : filePath.list()) {
                    new File(filePath, child).delete();
                }
            }
            
            ps = TARGET_CONNECTION.prepareStatement(queryStr);
            ps.setInt(1, 1); // 从结果集的第几条记录开始生成图片
            rs = ps.executeQuery();

            while (rs.next()) {
                fileName = rs.getString(1);
                Blob blob = rs.getBlob(2);
                int rownum = rs.getInt(3);
                logger.info("第" + rownum + "张图片：" + fileName);
                if (blob != null) {
                    dos = new DataOutputStream(new FileOutputStream(filePathStr + "/" + fileName));
                    dos.write(blob.getBytes(1L, (int) blob.length()));
                    dos.close();
                } else {
                    count++;
                }
            }
            
            logger.info("配件图片生成成功！空文件数：" + count);
        } catch (Exception e) {
            logger.error(fileName + "生成失败!!!");
            throw e;
        }finally{
            try {
                DbUtils.closeQuietly(rs);
                DbUtils.closeQuietly(ps);
                dos.close();
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }
    }

    /** 执行传入的SQ语句,根据结果集在默认目录生成图片.默认目录由config.propertiesli里的imageExportPath指定 */
    private void generateImages(GenerateImageTypeEnum generateImageType) throws Exception{
        generateImages(IMAGE_EXPORT_PATH, generateImageType);
    }
    
    private void generateBytes() {
        String filePathStr = "D:/PDS/image";
        Statement s = null;
        ResultSet rs = null;
        String fileName = null;
        DataOutputStream dos = null;
        BLOB blob = null;
        try {
            File filePath = new File(filePathStr);
            if (!filePath.exists()) {
                filePath.mkdirs();
            }
            s = TARGET_CONNECTION.createStatement();
            rs = s.executeQuery("SELECT FILENAME, FILEBODY FROM PDS_PARTS_FILES");
            
            while (rs.next()) {
                fileName = rs.getString(1);
                blob = (BLOB) rs.getBlob(2);
                dos = new DataOutputStream(new FileOutputStream(filePathStr + "/" + fileName));
                System.out.println(blob.length());
                dos.write(blob.getBytes(1L, (int) blob.length()));
                dos.write(blob.getBytes());
                dos.close();
            }
        } catch (Exception e) {
            DbUtils.closeQuietly(rs);
            DbUtils.closeQuietly(s);
            logger.error(fileName + "生成失败!");
            e.printStackTrace();
        }finally{
            try {
                dos.close();;
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }
    }

}
    
